// 阿里云数据库操作命令工具
const mysql = require('mysql2/promise');
const dotenv = require('dotenv');
const readline = require('readline');
const path = require('path');
const fs = require('fs');

// 尝试加载.env.local文件
const envPath = path.resolve(process.cwd(), '.env.local');
dotenv.config({ path: envPath });

// 如果环境变量未加载成功，尝试其他路径
if (!process.env.DB_HOST) {
  const parentEnvPath = path.resolve(process.cwd(), '..', '.env.local');
  if (fs.existsSync(parentEnvPath)) {
    dotenv.config({ path: parentEnvPath });
    console.log(`已从上级目录加载环境变量: ${parentEnvPath}`);
  }
}

// 数据库配置
const dbConfig = {
  host: process.env.DB_HOST || 'rm-bp14wd51i709c3z79ho.mysql.rds.aliyuncs.com', // 使用外网地址
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || 'Ircnjfdhnvxwz@123',
  database: process.env.DB_NAME || 'xiaoyoubao',
  port: process.env.DB_PORT || 3306,
  multipleStatements: true, // 允许执行多条SQL语句
  connectTimeout: 15000, // 增加连接超时时间
};

// 创建命令行交互界面
const rl = readline.createInterface({
  input: process.stdin,
  output: process.stdout
});

// 显示菜单
function showMenu() {
  console.log('\n阿里云数据库操作命令工具');
  console.log('------------------------');
  console.log('1. 查看所有表');
  console.log('2. 查看特定表结构');
  console.log('3. 执行自定义SQL查询');
  console.log('4. 备份特定表数据');
  console.log('5. 检查数据库连接');
  console.log('6. 退出');
  console.log('------------------------');

  rl.question('请选择操作 (1-6): ', (answer) => {
    switch(answer) {
      case '1':
        showAllTables();
        break;
      case '2':
        rl.question('请输入表名: ', (tableName) => {
          showTableStructure(tableName);
        });
        break;
      case '3':
        rl.question('请输入SQL查询语句: ', (sql) => {
          executeCustomSQL(sql);
        });
        break;
      case '4':
        rl.question('请输入要备份的表名: ', (tableName) => {
          backupTableData(tableName);
        });
        break;
      case '5':
        checkConnection();
        break;
      case '6':
        console.log('感谢使用，再见！');
        rl.close();
        process.exit(0);
        break;
      default:
        console.log('无效的选择，请重新选择。');
        showMenu();
        break;
    }
  });
}

// 检查数据库连接
async function checkConnection() {
  let connection;
  try {
    console.log('正在测试数据库连接...');
    console.log(`尝试连接: ${dbConfig.host}:${dbConfig.port}`);
    console.log(`数据库: ${dbConfig.database}`);
    console.log(`用户名: ${dbConfig.user}`);

    connection = await mysql.createConnection(dbConfig);
    console.log('数据库连接成功！');
  } catch (error) {
    console.error('数据库连接失败:', error.message);
    console.log('\n请检查以下可能的问题:');
    console.log('1. 确认数据库服务器地址和端口是否正确');
    console.log('2. 确认用户名和密码是否正确');
    console.log('3. 检查网络连接是否正常');
    console.log('4. 检查防火墙设置是否允许数据库连接');
    console.log('5. 确认已在阿里云控制台设置IP白名单');
  } finally {
    if (connection) await connection.end();
    showMenu();
  }
}

// 查看所有表
async function showAllTables() {
  let connection;
  try {
    connection = await mysql.createConnection(dbConfig);
    const [rows] = await connection.query('SHOW TABLES');

    console.log('\n数据库中的所有表:');
    console.log('------------------------');

    if (rows.length === 0) {
      console.log('数据库中没有表。');
    } else {
      rows.forEach((row, index) => {
        // 获取第一个键的值，即表名
        const tableName = Object.values(row)[0];
        console.log(`${index + 1}. ${tableName}`);
      });
    }
  } catch (error) {
    console.error('查询失败:', error.message);
  } finally {
    if (connection) await connection.end();
    showMenu();
  }
}

// 查看特定表结构
async function showTableStructure(tableName) {
  let connection;
  try {
    connection = await mysql.createConnection(dbConfig);
    const [rows] = await connection.query(`DESCRIBE ${tableName}`);

    console.log(`\n表 "${tableName}" 的结构:`);
    console.log('------------------------');

    if (rows.length === 0) {
      console.log('表不存在或没有列。');
    } else {
      console.log('字段名\t\t类型\t\t允许NULL\t键\t默认值\t\t额外信息');
      console.log('--------------------------------------------------------------------------------');
      rows.forEach(row => {
        console.log(`${row.Field}\t\t${row.Type}\t\t${row.Null}\t${row.Key || '-'}\t${row.Default || '-'}\t\t${row.Extra || '-'}`);
      });
    }
  } catch (error) {
    console.error('查询失败:', error.message);
  } finally {
    if (connection) await connection.end();
    showMenu();
  }
}

// 执行自定义SQL查询
async function executeCustomSQL(sql) {
  let connection;
  try {
    connection = await mysql.createConnection(dbConfig);
    const [result] = await connection.query(sql);

    console.log('\n查询结果:');
    console.log('------------------------');

    if (Array.isArray(result)) {
      if (result.length === 0) {
        console.log('查询结果为空。');
      } else {
        // 如果是查询结果，显示数据
        console.table(result);
        console.log(`查询返回 ${result.length} 条记录。`);
      }
    } else {
      // 如果是UPDATE, INSERT, DELETE等操作的结果
      console.log('操作成功完成。');
      console.log(`影响的行数: ${result.affectedRows}`);
      if (result.insertId) {
        console.log(`最后插入的ID: ${result.insertId}`);
      }
    }
  } catch (error) {
    console.error('查询失败:', error.message);
  } finally {
    if (connection) await connection.end();
    showMenu();
  }
}

// 备份特定表数据
async function backupTableData(tableName) {
  let connection;
  try {
    connection = await mysql.createConnection(dbConfig);

    // 获取表结构
    const [structure] = await connection.query(`SHOW CREATE TABLE ${tableName}`);
    const createTableSQL = structure[0]['Create Table'];

    // 获取表数据
    const [rows] = await connection.query(`SELECT * FROM ${tableName}`);

    console.log('\n表数据备份:');
    console.log('------------------------');
    console.log('表结构SQL:');
    console.log(createTableSQL + ';\n');

    if (rows.length === 0) {
      console.log('表中没有数据。');
    } else {
      console.log(`表中有 ${rows.length} 条记录。`);
      console.log('数据插入SQL示例:');

      // 生成INSERT语句
      const columns = Object.keys(rows[0]).join(', ');
      console.log(`INSERT INTO ${tableName} (${columns}) VALUES`);

      // 显示前5条数据作为示例
      const limit = Math.min(5, rows.length);
      for (let i = 0; i < limit; i++) {
        const values = Object.values(rows[i]).map(val => {
          if (val === null) return 'NULL';
          if (typeof val === 'string') return `'${val.replace(/'/g, "''")}'`;
          if (val instanceof Date) return `'${val.toISOString().slice(0, 19).replace('T', ' ')}'`;
          return val;
        }).join(', ');

        console.log(`(${values})${i < limit - 1 ? ',' : ';'}`);
      }

      if (rows.length > 5) {
        console.log(`... 省略了 ${rows.length - 5} 条记录`);
      }
    }
  } catch (error) {
    console.error('备份失败:', error.message);
  } finally {
    if (connection) await connection.end();
    showMenu();
  }
}

// 启动应用
console.log('正在连接到阿里云数据库...');
checkConnection();